--创建自定义数据类型
create type my_type as
(
    name varchar(10),
    age  smallint
);

-- 创建表测试调用存储过程
create table if not exists stored_procedure_drill
(
    id                serial primary key, -- id 自增
    name              varchar(20) not null,
    age               smallint    not null,
    exercise_duration varchar(10) not null default '一坤年',
    hobbies           varchar(30),
    birth_year        date        not null,
    job               varchar(20),
    purses            integer[],
    very_good_friends my_type,
    create_time       timestamp,
    update_time       timestamp
);

drop table stored_procedure_drill;
comment on column stored_procedure_drill.id IS '主键id';
comment on column stored_procedure_drill.name IS '姓名';
comment on column stored_procedure_drill.age IS '年龄';
comment on column stored_procedure_drill.exercise_duration IS '练习时长';
comment on column stored_procedure_drill.hobbies IS '兴趣爱好';
comment on column stored_procedure_drill.birth_year IS '生日';
comment on column stored_procedure_drill.job IS '工作';
comment on column stored_procedure_drill.purses IS '钱包';
comment on column stored_procedure_drill.very_good_friends IS '好朋友';
comment on column stored_procedure_drill.create_time IS '创建时间';
comment on column stored_procedure_drill.update_time IS '修改时间';




DROP TRIGGER trigger_update_last_updated
    ON  stored_procedure_drill;
-- 创建存储过程-》新增
create or replace procedure
              saveData( name varchar, age smallint, exercise_duration varchar
                          , hobbies varchar, birth_year date, job varchar, purses integer[]
                          , very_good_friends my_type)
                      as
$$
begin
insert into stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends)
values (name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends);
end
$$
    language plpgsql;

drop procedure saveData;
drop procedure updateData;
drop procedure deleteData;
drop procedure getData;
-- 调用存储过程
call saveData(cast('1' as varchar), cast(1 as smallint), cast('1' as varchar), cast('1' as varchar)
    , cast('2023-01-02' as date), cast('1' as varchar), cast('{1, 2, 3}' as integer[]),
              (cast('zhangsan' as varchar), cast(18 as smallint))
    );


-- 创建存储过程-》修改
create or replace procedure
              updateData(id1 integer, name1 varchar, age1 smallint, exercise_duration1 varchar
                            , hobbies1 varchar, birth_year1 date, job1 varchar, purses1 integer[]
                            , very_good_friends1 my_type)
                        as
$$
declare
    count_num integer;
begin
select count(*) into count_num from stored_procedure_drill where id = id1;
if count_num < 1 then
insert into stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends)
values (name1, age1, exercise_duration1, hobbies1, birth_year1, job1, purses1, very_good_friends1);
raise notice '没有此数据，默认新增';
else
update stored_procedure_drill
set name              = name1
  , age               =age1
  , exercise_duration = exercise_duration1
  , hobbies           = hobbies1
  , birth_year        = birth_year1
  , job               = job1
  , purses            = purses1
  , very_good_friends = very_good_friends1
where id = id1;
raise notice '修改成功';
end if;
end
$$
    language plpgsql;

call updateData(2, cast('1' as varchar), cast(55 as smallint), cast('1' as varchar), cast('1' as varchar)
    , cast('2023-01-02' as date), cast('1' as varchar), cast('{1, 2, 3}' as integer[]),
                (cast('zhangsan' as varchar), cast(18 as smallint))
    );


-- -- 创建存储过程-》删除
create or replace procedure deleteData(id1 integer)
              as
              $$
              declare
              count_num integer;
begin
select count(*) into count_num from stored_procedure_drill where id = id1;
if count_num < 1 then
        raise exception '没有这条数据，无法删除';
else
delete from stored_procedure_drill where id = id1;
end if;
end
$$
    language plpgsql;

call deleteData(1);

-- 创建函数-》查询
create or replace FUNCTION selectData( page_num integer, page_size integer)
              returns setof stored_procedure_drill
              as
              $$
begin
    return query select id,
                        name,
                        age,
                        exercise_duration,
                        hobbies,
                        birth_year,
                        job,
                        purses,
                        very_good_friends,
                        create_time,
                        update_time
                 from stored_procedure_drill
                 order by update_time desc
                 limit page_size offset (page_num - 1) * page_size;
end
$$
    language plpgsql;
DROP FUNCTION selectdata(integer, integer, integer);

-- 调用函数
select selectData(null, null, null);
select selectData(1,10);

-- 创建存储过程-》查询
create or replace procedure getData(page_num integer, page_size integer,out arr REFCURSOR)
              as
              $$
begin
    open arr for   select id,
                          name,
                          age,
                          exercise_duration,
                          hobbies,
                          birth_year,
                          job,
                          purses,
                          very_good_friends,
                          create_time,
                          update_time
                   from stored_procedure_drill
                   order by update_time desc
                   limit page_size offset (page_num - 1) * page_size;
end
$$
    language plpgsql;



-- 调用存储过程
do $$
    DECLARE
        output_val  REFCURSOR;
em stored_procedure_drill%rowtype;
cursor_exists boolean := false;
begin
call getData(cast(null as integer), cast(null as integer), output_val);
cursor_exists := FOUND; -- Check if cursor exists
loop
            fetch output_val into em;
exit when em is null;
raise notice '%,%,%,%,%,%,%,%,%,%,%',em.id,em.name,em.age,em.exercise_duration,em.hobbies,em.birth_year,em.job,em.purses,em.very_good_friends,em.create_time,em.update_time;
end loop;
close output_val;
end
    $$
language plpgsql;

-- 创建函数调用查询数据的存储过程
create FUNCTION getSelectPro(page_num integer, page_size integer)
    returns  refcursor
as $$
    declare
        output_val  REFCURSOR;
em stored_procedure_drill%rowtype;
cursor_exists boolean := false;
arr varchar[];
a1 varchar;
begin
call getData(cast(page_num as integer), cast(page_size as integer), output_val);
cursor_exists := FOUND; -- Check if cursor exists
loop
            fetch output_val into em;
exit when em is null;
raise notice '%,%,%,%,%,%,%,%,%,%,%',cast(em.id as integer) ,cast(em.name as varchar),cast(em.age as smallint),cast(em.exercise_duration as varchar),cast(em.hobbies as varchar),cast(em.birth_year as date)
                ,cast(em.job as varchar),cast(em.purses as integer[]),cast(em.very_good_friends as my_type),cast(em.create_time as timestamp),cast(em.update_time as timestamp);

a1 :=  array_to_string(ARRAY(SELECT unnest(ARRAY[(cast(em.id as integer) ,cast(em.name as varchar),cast(em.age as smallint),cast(em.exercise_duration as varchar),cast(em.hobbies as varchar),cast(em.birth_year as date)
                ,cast(em.job as varchar),cast(em.purses as integer[]),cast(em.very_good_friends as my_type),cast(em.create_time as timestamp),cast(em.update_time as timestamp))])), ',') AS formatted_record;
arr := array_append(arr,a1);
end loop;
close output_val;
return arr;
end
$$
    language plpgsql;

drop FUNCTION getSelectPro();
-- 调用函数
select getSelectPro(1,10);
-- {"(12,test,18,两年半,唱、跳、RAP、篮球,1990-11-20,无业,\"{1,2,3,4,5,6,7,8,9,10}\",\"(妮基肽酶,20)\",\"2024-05-05 09:45:09.035562\",\"2024-05-05 09:45:09.035562\")","(11,test,18,两年半,唱、跳、RAP、篮球,2008-12-02,无业,\"{1,2,3,4,5,6,7,8,9,10}\",\"(妮基肽酶,20)\",\"2024-05-04 23:26:53.094757\",\"2024-05-04 23:26:53.094757\")","(8,发放,0,阿萨法发,发生发,2024-04-02,萨法,\"{100,200,233,662}\",\"(guozhiwen,0)\",\"2024-05-04 04:13:31.851788\",\"2024-05-04 23:26:49.389214\")","(10,test,18,两年半,唱、跳、RAP、篮球,1969-04-20,无业,\"{1,2,3,4,5,6,7,8,9,10}\",\"(妮基肽酶,20)\",\"2024-05-04 15:55:45.081356\",\"2024-05-04 15:55:45.081356\")","(3,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:04:46.217191\",\"2024-05-04 09:38:12.882297\")","(4,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:04:46.217191\",\"2024-05-04 09:38:12.882297\")","(7,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:13:31.066924\",\"2024-05-04 04:13:31.066924\")","(1,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:13:11.149997\",\"2024-05-04 04:13:11.149997\")","(5,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:04:46.217191\",\"2024-05-04 04:04:46.217191\")"}`
-- 创建存储过程统计
create or replace procedure getCount(c1 out INTEGER)
              as
              $$
begin
select count(*) into c1
from stored_procedure_drill ;
end
$$
    language plpgsql;

create FUNCTION getCountPro()
    returns  integer
    as $$
    DECLARE
        c integer;
begin
call getCount(c);
return c;
end
$$
language plpgsql;

select getCountPro();
-- 触发器
-- 创建数据变更记录表
create table if not exists data_change_log_sheet_for_stored_procedure_drill

(
    id   serial  primary key,
    name              varchar(20) not null,
    age               smallint    not null,
    exercise_duration varchar(10) not null default '一坤年',
    hobbies           varchar(30),
    birth_year        date        not null,
    job               varchar(20),
    purses            integer[],
    very_good_friends my_type,
    operation_time       timestamp, -- 操作时间
    operation_type varchar(10)  -- 操作类型
);

-- 使用触发器实现时间自动更新
-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_last_updated_column()
              RETURNS TRIGGER AS
              $$
BEGIN
    if tg_op = 'INSERT' then
        NEW.create_time = NOW();
NEW.update_time = NOW();
elsif TG_OP = 'UPDATE' then
        NEW.update_time = NOW();
end if;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_last_updated
    BEFORE INSERT OR UPDATE
    ON stored_procedure_drill
    FOR EACH ROW
EXECUTE FUNCTION update_last_updated_column();

-- 创建触发器函数
CREATE OR REPLACE FUNCTION recording_data_logs()
              RETURNS TRIGGER AS
              $$
BEGIN
    IF TG_OP = 'INSERT' THEN
INSERT INTO data_change_log_sheet_for_stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends, operation_time, operation_type)
VALUES (new.name,new.age, new.exercise_duration, new.hobbies, new.birth_year, new.job, new.purses, new.very_good_friends, now(), 'INSERT');
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO data_change_log_sheet_for_stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends, operation_time, operation_type)
VALUES (old.name,old.age, old.exercise_duration, old.hobbies, old.birth_year, old.job, old.purses, old.very_good_friends, now(), 'UPDATE');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;


-- 创建触发器函数删除
CREATE OR REPLACE FUNCTION recording_data_logs_delete()
              RETURNS TRIGGER AS
              $$
BEGIN
    IF TG_OP = 'DELETE' THEN
INSERT INTO data_change_log_sheet_for_stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends, operation_time, operation_type)
VALUES (old.name,old.age, old.exercise_duration, old.hobbies, old.birth_year, old.job, old.purses, old.very_good_friends, now(), 'DELETE');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- 触发器  删除
CREATE TRIGGER trigger_recording_data_logs_delete
    AFTER DELETE
    ON stored_procedure_drill
    FOR EACH ROW
    EXECUTE FUNCTION recording_data_logs_delete();

-- 创建触发器
CREATE TRIGGER trigger_recording_data_logs
    BEFORE INSERT OR UPDATE
    ON stored_procedure_drill
    FOR EACH ROW
EXECUTE FUNCTION recording_data_logs();
